<?php 
    include_once "backend/classes/User.php";
    session_start();
    include_once "backend/session.php";
    if (!isLoggedIn()) {exit();}
    if ($_SESSION['User']->UserType != 'Manager') {exit();}
    include_once "backend/db/db_cse305.php";
    
    if (!isset($_GET["type"])) {
        exit();
    }
?>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title> Sales Report </title>
        <link rel="stylesheet" type="text/css" href="css/style.css">
        <link rel="stylesheet" type="text/css" href="css/main.css">
        <script src="js/sortable.js"></script>
    </head>
    <body>
        <br />
        <?php
        
            $type = $_GET["type"];
            $where = "";
            if ($type == "time_range") {
                if (!isset($_GET["start"]) || !isset($_GET["end"]) ) {
                    exit();
                }
                $s = $_GET["start"];
                $e = $_GET["end"];
                if (strlen($s) != 10 || strlen($e) != 10 || !validDate($s) || !validDate($e)) {
                    exit();
                }
                echo "<center><h1>Sales Report For Time Range: " . $s . " to " . $e . "</h1></center><br/>" ;
                $query = sprintf("SELECT * FROM orders WHERE Time >= '%s 00:00:00' && Time <= '%s 00:00:00' ORDER BY Time", 
                    mysql_real_escape_string($s), mysql_real_escape_string($e));
                $where = sprintf(" orders.Time >= '%s 00:00:00' && orders.Time <= '%s 00:00:00'", mysql_real_escape_string($s), mysql_real_escape_string($e));
            } else if ($type == "stock_symbol") {
                if (!isset($_GET["q"])) {
                    exit();
                }
                $q = $_GET["q"];
                echo "<center><h1>Sales Report For Stock: " . $q . "</h1></center><br/>" ;
                $query = sprintf("SELECT * FROM orders WHERE StockSymbol = '%s' ORDER BY TIME", 
                    mysql_real_escape_string($q));
                $where = sprintf(" orders.StockSymbol = '%s'", mysql_real_escape_string($q));
            } else if ($type == "stock_type") {
                if (!isset($_GET["q"])) {
                    exit();
                }
                $q = $_GET["q"];
                echo "<center><h1>Sales Report For Stock Type: " . $q . "</h1></center><br/>" ;
                $query = sprintf("SELECT * FROM orders JOIN stocks USING (StockSymbol) WHERE StockType = '%s' ORDER BY TIME", 
                    mysql_real_escape_string($q));
                $where = sprintf(" stocks.StockType = '%s'", mysql_real_escape_string($q));
            } else if ($type == "customer_id") {
                if (!isset($_GET["q"])) {
                    exit();
                }
                $q = $_GET["q"];
                
                $customerName = "";
                $query = sprintf("SELECT * FROM users WHERE UserId = %d && UserType = 'Customer'", 
                        mysql_real_escape_string($q));
                $result = mysql_query($query) or die(mysql_error());
                if ($row = mysql_fetch_array($result)) {
                    if ($row["UserType"] != 'Customer') {
                        echo "No customer exists with that User ID.";
                        exit();
                    }
                    $customerName = $row["FirstName"] . " " . $row["LastName"];
                } else {
                    echo "No customer exists with that User ID.";
                    exit();
                }
                
                $custAccounts = array();
                $query = sprintf("SELECT * FROM accounts WHERE CustomerUserID = %d", 
                        mysql_real_escape_string($q));
                $result = mysql_query($query) or die(mysql_error());
                $any = false;
                while ($row = mysql_fetch_array($result)) {
                    $any = true;
                    $custAccounts[] = $row["AccountID"];
                }
                if (!$any) {
                    echo "Customer has no accounts!";
                    exit();
                }
                $where = implode(" , ", $custAccounts);
                $where = "(" . $where . ")";
                
                echo "<center><h1>Sales Report For Customer: " . $customerName . " (" . $q . ")</h1></center><br/>" ;
                $query = sprintf("SELECT * FROM orders JOIN stocks USING (StockSymbol) WHERE AccountID IN %s ORDER BY TIME", 
                    mysql_real_escape_string($where));
                $where = sprintf(" users.UserId = %d", mysql_real_escape_string($q));
            } else {
                echo "Invalid type.";
                exit();
            }
            $result = mysql_query($query) or die(mysql_error());
            if (mysql_num_rows($result) == 0) {
                echo "No sales with the specified query.";
                exit();
            }
            $sell = 0; $sellShares = 0; $sellFee = 0;
            $buy = 0; $buyShares = 0; $buyFee = 0;
            $sellStock = array();
            $buyStock = array();
            $totalStock = array(); 
            while ($row = mysql_fetch_array($result)) {
                if ($row["OrderType"] == "sell") {
                    $sell++;
                    $sellShares += $row["NumShares"];
                    $sellFee += $row["Fee"];
                    if (!isset($sellStock[$row["StockSymbol"]])) {
                        $sellStock[$row["StockSymbol"]] = $row["NumShares"];
                    } else {
                        $sellStock[$row["StockSymbol"]] += $row["NumShares"];
                    }
                } else {
                    $buy++;
                    $buyShares += $row["NumShares"];
                    $buyFee += $row["Fee"];
                    if (!isset($buyStock[$row["StockSymbol"]])) {
                        $buyStock[$row["StockSymbol"]] = $row["NumShares"];
                    } else {
                        $buyStock[$row["StockSymbol"]] += $row["NumShares"];
                    }
                }
                if (!isset($totalStock[$row["StockSymbol"]])) {
                    $totalStock[$row["StockSymbol"]] = $row["NumShares"];
                } else {
                    $totalStock[$row["StockSymbol"]] += $row["NumShares"];
                }
            }
            //TODO Print Info
            
            
            echo "<table class='rounded-corner'>";
            echo "<thead style='text-align:center;'><tr><th></th><th><b>Total</b></th><th><b>From Sells</b></th><th><b>From Buys</b></th></tr></thead>";
            echo "<tr><td style='width:40%;'><b>Number of Transactions:</b></td><td>" . ($sell + $buy) . "</td><td>" . $sell . "</td><td>" . $buy . "</td></tr>";
            echo "<tr><td style='width:40%;'><b>Shares Bought and Sold:</b></td><td>" . ($sellShares + $buyShares) . "</td><td>" . $sellShares . "</td><td>" . $buyShares . "</td></tr>";
            echo "<tr><td style='width:40%;'><b>Fees Generated:</b></td><td>" . ($sellFee + $buyFee) . "</td><td>" . $sellFee . "</td><td>" . $buyFee . "</td></tr>";
            echo "</table><br/><hr/>";
            
            echo "<h3>Stock Sales Reports</h3><br/>";
            echo "<table id='stock_sales_report' class='rounded-corner sortable'>";
            echo "<thead style='text-align:center;'><tr><th><b>Stock Symbol</b></th><th><b>Total Shares Bought and Sold</b></th><th><b>Shares Bought</b></th><th><b>Shares Sold</b></th></tr></thead>";
            echo "<tbody id='stock_sales_report_body'>";
            foreach ($totalStock as $StockSym => $NumShares) {
                echo "<tr><td><b>" . $StockSym . "</b></td><td>" . $NumShares . "</td>";
                if (isset($buyStock[$StockSym])) {
                    echo "<td>" . $buyStock[$StockSym] . "</td>";
                } else {
                    echo "<td>" . 0 . "</td>";
                }
                if (isset($sellStock[$StockSym])) {
                    echo "<td>" . $sellStock[$StockSym] . "</td>";
                } else {
                    echo "<td>" . 0 . "</td>";
                }
                echo "</tr>";
            }
            echo "</tbody></table><br/><hr/><br/>";
            
            
            ///////////////////////////////
            
            echo "<h3>Highest Revenue Employees</h3>";
            echo "<table id='cr_rev' class='rounded-corner sortable'>";
            echo "<thead style='text-align:center;'><tr><th><b>Employee Name</b></th><th><b>Employee ID</b></th><th><b>Total Fees Collected</b></th></tr></thead>";
            echo "<tbody id='cr_rev_body'>";
            $query = sprintf("SELECT *, SUM(FEE) AS `sum` FROM users JOIN employees USING (UserID) JOIN orders ON (EmployeeID = employees.UserID) WHERE %s GROUP BY employees.UserID ORDER BY `sum` DESC LIMIT 5"
                    , $where);
            $result = mysql_query($query) or die(mysql_error());
            while ($row = mysql_fetch_array($result)) {
                echo "<tr><td>" . $row["FirstName"] . " " . $row["LastName"] . "</td>";
                echo "<td>".$row["EmployeeID"]."</td>";
                echo "<td>".$row["sum"]."</td>";
                echo "</tr>";
            }
            echo "</tbody></table><br/><hr/><br/>";
            
            
            
            echo "<h3>Highest Revenue Customers</h3>";
            echo "<table id='cust_rev' class='rounded-corner sortable'>";
            echo "<thead style='text-align:center;'><tr><th><b>Customer Name</b></th><th><b>Customer ID</b></th><th><b>Total Fees Collected</b></th></tr></thead>";
            echo "<tbody id='cust_rev_body'>";
            $query = sprintf("SELECT *, SUM(FEE) AS `sum` FROM users JOIN customer ON (users.UserId = customer.UserID) JOIN accounts ON (accounts.CustomerUserID = customer.UserID) JOIN orders ON (orders.AccountID = accounts.AccountID) WHERE %s GROUP BY customer.UserID ORDER BY `sum` DESC LIMIT 5"
                    , $where);
            $result = mysql_query($query) or die(mysql_error());
            while ($row = mysql_fetch_array($result)) {
                echo "<tr><td>" . $row["FirstName"] . " " . $row["LastName"] . "</td>";
                echo "<td>".$row["CustomerUserID"]."</td>";
                echo "<td>".$row["sum"]."</td>";
                echo "</tr>";
            }
            echo "</tbody></table><br/><hr/><br/>";
            
            ///////////////////////////////
            
            function validDate($v) {
                return preg_match('/^(19|20|21)\d\d[-](0[1-9]|1[012])[-](0[1-9]|[12][0-9]|3[01])$/', $v) && 
                    checkdate(substr($v, 5, 2),substr($v, 8, 2),substr($v, 0, 4));
            }
        ?>
    </body>
</html>
